package com.bookworld.dao;

import com.bookworld.model.Order;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
@Mapper
public interface OrderDao {
    
    @Select("SELECT * FROM orders WHERE id = #{id}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "orderNumber", column = "order_number"),
        @Result(property = "userId", column = "user_id"),
        @Result(property = "orderDate", column = "order_date"),
        @Result(property = "totalAmount", column = "total_amount"),
        @Result(property = "discount", column = "discount"),
        @Result(property = "tax", column = "tax"),
        @Result(property = "shippingFee", column = "shipping_fee"),
        @Result(property = "finalAmount", column = "final_amount"),
        @Result(property = "status", column = "status"),
        @Result(property = "paymentMethod", column = "payment_method"),
        @Result(property = "paymentStatus", column = "payment_status"),
        @Result(property = "shippingName", column = "shipping_name"),
        @Result(property = "shippingAddress", column = "shipping_address"),
        @Result(property = "shippingCity", column = "shipping_city"),
        @Result(property = "shippingCountry", column = "shipping_country"),
        @Result(property = "shippingPostalCode", column = "shipping_postal_code"),
        @Result(property = "shippingPhone", column = "shipping_phone"),
        @Result(property = "shippingEmail", column = "shipping_email"),
        @Result(property = "trackingNumber", column = "tracking_number"),
        @Result(property = "courier", column = "courier"),
        @Result(property = "notes", column = "notes"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updatedAt", column = "updated_at")
    })
    Order findById(Long id);
    
    @Select("SELECT * FROM orders WHERE order_number = #{orderNumber}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "orderNumber", column = "order_number"),
        @Result(property = "userId", column = "user_id"),
        @Result(property = "orderDate", column = "order_date"),
        @Result(property = "totalAmount", column = "total_amount"),
        @Result(property = "discount", column = "discount"),
        @Result(property = "tax", column = "tax"),
        @Result(property = "shippingFee", column = "shipping_fee"),
        @Result(property = "finalAmount", column = "final_amount"),
        @Result(property = "status", column = "status"),
        @Result(property = "paymentMethod", column = "payment_method"),
        @Result(property = "paymentStatus", column = "payment_status"),
        @Result(property = "shippingName", column = "shipping_name"),
        @Result(property = "shippingAddress", column = "shipping_address"),
        @Result(property = "shippingCity", column = "shipping_city"),
        @Result(property = "shippingCountry", column = "shipping_country"),
        @Result(property = "shippingPostalCode", column = "shipping_postal_code"),
        @Result(property = "shippingPhone", column = "shipping_phone"),
        @Result(property = "shippingEmail", column = "shipping_email"),
        @Result(property = "trackingNumber", column = "tracking_number"),
        @Result(property = "courier", column = "courier"),
        @Result(property = "notes", column = "notes"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updatedAt", column = "updated_at")
    })
    Order findByOrderNumber(String orderNumber);
    
    @Select("SELECT * FROM orders WHERE user_id = #{userId} ORDER BY order_date DESC LIMIT #{limit} OFFSET #{offset}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "orderNumber", column = "order_number"),
        @Result(property = "userId", column = "user_id"),
        @Result(property = "orderDate", column = "order_date"),
        @Result(property = "totalAmount", column = "total_amount"),
        @Result(property = "discount", column = "discount"),
        @Result(property = "tax", column = "tax"),
        @Result(property = "shippingFee", column = "shipping_fee"),
        @Result(property = "finalAmount", column = "final_amount"),
        @Result(property = "status", column = "status"),
        @Result(property = "paymentMethod", column = "payment_method"),
        @Result(property = "paymentStatus", column = "payment_status"),
        @Result(property = "shippingName", column = "shipping_name"),
        @Result(property = "shippingAddress", column = "shipping_address"),
        @Result(property = "shippingCity", column = "shipping_city"),
        @Result(property = "shippingCountry", column = "shipping_country"),
        @Result(property = "shippingPostalCode", column = "shipping_postal_code"),
        @Result(property = "shippingPhone", column = "shipping_phone"),
        @Result(property = "shippingEmail", column = "shipping_email"),
        @Result(property = "trackingNumber", column = "tracking_number"),
        @Result(property = "courier", column = "courier"),
        @Result(property = "notes", column = "notes"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updatedAt", column = "updated_at")
    })
    List<Order> findByUserId(Long userId, int limit, int offset);
    
    @Select("SELECT * FROM orders WHERE status = #{status} ORDER BY order_date DESC LIMIT #{limit} OFFSET #{offset}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "orderNumber", column = "order_number"),
        @Result(property = "userId", column = "user_id"),
        @Result(property = "orderDate", column = "order_date"),
        @Result(property = "totalAmount", column = "total_amount"),
        @Result(property = "discount", column = "discount"),
        @Result(property = "tax", column = "tax"),
        @Result(property = "shippingFee", column = "shipping_fee"),
        @Result(property = "finalAmount", column = "final_amount"),
        @Result(property = "status", column = "status"),
        @Result(property = "paymentMethod", column = "payment_method"),
        @Result(property = "paymentStatus", column = "payment_status"),
        @Result(property = "shippingName", column = "shipping_name"),
        @Result(property = "shippingAddress", column = "shipping_address"),
        @Result(property = "shippingCity", column = "shipping_city"),
        @Result(property = "shippingCountry", column = "shipping_country"),
        @Result(property = "shippingPostalCode", column = "shipping_postal_code"),
        @Result(property = "shippingPhone", column = "shipping_phone"),
        @Result(property = "shippingEmail", column = "shipping_email"),
        @Result(property = "trackingNumber", column = "tracking_number"),
        @Result(property = "courier", column = "courier"),
        @Result(property = "notes", column = "notes"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updatedAt", column = "updated_at")
    })
    List<Order> findByStatus(String status, int limit, int offset);
    
    @Select("SELECT * FROM orders WHERE payment_status = #{paymentStatus} ORDER BY order_date DESC LIMIT #{limit} OFFSET #{offset}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "orderNumber", column = "order_number"),
        @Result(property = "userId", column = "user_id"),
        @Result(property = "orderDate", column = "order_date"),
        @Result(property = "totalAmount", column = "total_amount"),
        @Result(property = "discount", column = "discount"),
        @Result(property = "tax", column = "tax"),
        @Result(property = "shippingFee", column = "shipping_fee"),
        @Result(property = "finalAmount", column = "final_amount"),
        @Result(property = "status", column = "status"),
        @Result(property = "paymentMethod", column = "payment_method"),
        @Result(property = "paymentStatus", column = "payment_status"),
        @Result(property = "shippingName", column = "shipping_name"),
        @Result(property = "shippingAddress", column = "shipping_address"),
        @Result(property = "shippingCity", column = "shipping_city"),
        @Result(property = "shippingCountry", column = "shipping_country"),
        @Result(property = "shippingPostalCode", column = "shipping_postal_code"),
        @Result(property = "shippingPhone", column = "shipping_phone"),
        @Result(property = "shippingEmail", column = "shipping_email"),
        @Result(property = "trackingNumber", column = "tracking_number"),
        @Result(property = "courier", column = "courier"),
        @Result(property = "notes", column = "notes"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updatedAt", column = "updated_at")
    })
    List<Order> findByPaymentStatus(String paymentStatus, int limit, int offset);
    
    @Select("SELECT * FROM orders ORDER BY order_date DESC LIMIT #{limit} OFFSET #{offset}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "orderNumber", column = "order_number"),
        @Result(property = "userId", column = "user_id"),
        @Result(property = "orderDate", column = "order_date"),
        @Result(property = "totalAmount", column = "total_amount"),
        @Result(property = "discount", column = "discount"),
        @Result(property = "tax", column = "tax"),
        @Result(property = "shippingFee", column = "shipping_fee"),
        @Result(property = "finalAmount", column = "final_amount"),
        @Result(property = "status", column = "status"),
        @Result(property = "paymentMethod", column = "payment_method"),
        @Result(property = "paymentStatus", column = "payment_status"),
        @Result(property = "shippingName", column = "shipping_name"),
        @Result(property = "shippingAddress", column = "shipping_address"),
        @Result(property = "shippingCity", column = "shipping_city"),
        @Result(property = "shippingCountry", column = "shipping_country"),
        @Result(property = "shippingPostalCode", column = "shipping_postal_code"),
        @Result(property = "shippingPhone", column = "shipping_phone"),
        @Result(property = "shippingEmail", column = "shipping_email"),
        @Result(property = "trackingNumber", column = "tracking_number"),
        @Result(property = "courier", column = "courier"),
        @Result(property = "notes", column = "notes"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updatedAt", column = "updated_at")
    })
    List<Order> findAllWithPagination(int limit, int offset);
    
    @Select("SELECT COUNT(*) FROM orders")
    int count();
    
    @Select("SELECT COUNT(*) FROM orders WHERE user_id = #{userId}")
    int countByUserId(Long userId);
    
    @Select("SELECT COUNT(*) FROM orders WHERE status = #{status}")
    int countByStatus(String status);
    
    @Select("SELECT COUNT(*) FROM orders WHERE payment_status = #{paymentStatus}")
    int countByPaymentStatus(String paymentStatus);
    
    @Insert("INSERT INTO orders (order_number, user_id, order_date, total_amount, discount, tax, shipping_fee, final_amount, status, payment_method, payment_status, shipping_name, shipping_address, shipping_city, shipping_country, shipping_postal_code, shipping_phone, shipping_email, tracking_number, courier, notes, created_at, updated_at) " +
            "VALUES (#{orderNumber}, #{userId}, #{orderDate}, #{totalAmount}, #{discount}, #{tax}, #{shippingFee}, #{finalAmount}, #{status}, #{paymentMethod}, #{paymentStatus}, #{shippingName}, #{shippingAddress}, #{shippingCity}, #{shippingCountry}, #{shippingPostalCode}, #{shippingPhone}, #{shippingEmail}, #{trackingNumber}, #{courier}, #{notes}, #{createdAt}, #{updatedAt})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void insert(Order order);
    
    @Update("UPDATE orders SET order_number=#{orderNumber}, user_id=#{userId}, order_date=#{orderDate}, total_amount=#{totalAmount}, discount=#{discount}, tax=#{tax}, shipping_fee=#{shippingFee}, final_amount=#{finalAmount}, status=#{status}, payment_method=#{paymentMethod}, payment_status=#{paymentStatus}, shipping_name=#{shippingName}, shipping_address=#{shippingAddress}, shipping_city=#{shippingCity}, shipping_country=#{shippingCountry}, shipping_postal_code=#{shippingPostalCode}, shipping_phone=#{shippingPhone}, shipping_email=#{shippingEmail}, tracking_number=#{trackingNumber}, courier=#{courier}, notes=#{notes}, updated_at=#{updatedAt} WHERE id=#{id}")
    void update(Order order);
    
    @Update("UPDATE orders SET status=#{status} WHERE id=#{id}")
    void updateStatus(@Param("id") Long id, @Param("status") String status);
    
    @Update("UPDATE orders SET payment_status=#{paymentStatus} WHERE id=#{id}")
    void updatePaymentStatus(@Param("id") Long id, @Param("paymentStatus") String paymentStatus);
    
    @Update("UPDATE orders SET tracking_number=#{trackingNumber}, courier=#{courier} WHERE id=#{id}")
    void updateTrackingInfo(@Param("id") Long id, @Param("trackingNumber") String trackingNumber, @Param("courier") String courier);
    
    @Delete("DELETE FROM orders WHERE id = #{id}")
    void delete(Long id);
    
    @Select("SELECT * FROM orders WHERE order_number LIKE CONCAT('%', #{keyword}, '%') OR shipping_name LIKE CONCAT('%', #{keyword}, '%') OR shipping_email LIKE CONCAT('%', #{keyword}, '%') ORDER BY order_date DESC LIMIT #{limit} OFFSET #{offset}")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "orderNumber", column = "order_number"),
        @Result(property = "userId", column = "user_id"),
        @Result(property = "orderDate", column = "order_date"),
        @Result(property = "totalAmount", column = "total_amount"),
        @Result(property = "discount", column = "discount"),
        @Result(property = "tax", column = "tax"),
        @Result(property = "shippingFee", column = "shipping_fee"),
        @Result(property = "finalAmount", column = "final_amount"),
        @Result(property = "status", column = "status"),
        @Result(property = "paymentMethod", column = "payment_method"),
        @Result(property = "paymentStatus", column = "payment_status"),
        @Result(property = "shippingName", column = "shipping_name"),
        @Result(property = "shippingAddress", column = "shipping_address"),
        @Result(property = "shippingCity", column = "shipping_city"),
        @Result(property = "shippingCountry", column = "shipping_country"),
        @Result(property = "shippingPostalCode", column = "shipping_postal_code"),
        @Result(property = "shippingPhone", column = "shipping_phone"),
        @Result(property = "shippingEmail", column = "shipping_email"),
        @Result(property = "trackingNumber", column = "tracking_number"),
        @Result(property = "courier", column = "courier"),
        @Result(property = "notes", column = "notes"),
        @Result(property = "createdAt", column = "created_at"),
        @Result(property = "updatedAt", column = "updated_at")
    })
    List<Order> search(String keyword, int limit, int offset);
}
